package cn.buk.tms.report.service;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;

public class BaseReportExportExcel {

  protected static Row getRow(Sheet sheet, int curDataRow) {
    while (sheet.getLastRowNum() < curDataRow) {
      sheet.createRow(sheet.getLastRowNum() + 1);
    }
    return sheet.getRow(curDataRow);
  }

  protected static double evaluateTotal(final String cellRef, Sheet sheet) {
    FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    CellReference cellReference = new CellReference(cellRef);
    Row row1 = sheet.getRow(cellReference.getRow());
    Cell cell1 = row1.getCell(cellReference.getCol());
    CellValue cellValue = evaluator.evaluate(cell1);

    return cellValue.getNumberValue();
  }

  /**
   * 创建标题行
   * @param title 标题内容
   * @param sheet 工作表
   * @param currentRowNo 行号
   * @param firstCol 开始列号
   * @param lastCol 截止列号
   * @return
   */
  protected static Row createTitleRow(final String title,
                                      final Sheet sheet,
                                      final int currentRowNo,
                                      final int firstCol,
                                      final int lastCol) {
    Row row = getRow(sheet, currentRowNo);
    row.setHeightInPoints(30);
    Cell startCell = row.createCell(0);
    startCell.setCellValue(title);
    CellRangeAddress region = new CellRangeAddress(currentRowNo, currentRowNo, firstCol, lastCol);
    sheet.addMergedRegion(region);

    startCell.setCellStyle(createTitleStyle(sheet));

    return row;
  }

  /**
   * 创建表头行
   * @return
   */
  protected static Row createTableHeaderRow(final Sheet sheet,
                                            final int currentRowNo,
                                            final String... colNames) {
    Row row = getRow(sheet, currentRowNo);
    int col = -1;
    CellStyle cellStyle = createTableHeaderStyle(sheet);
    for(String colName: colNames) {
      col++;
      Cell cell = row.createCell(col);
      cell.setCellValue(colName);
      cell.setCellStyle(cellStyle);
    }

    return row;
  }

  /**
   * 设置默认列宽，width为字符个数
   *    sheet.setDefaultColumnWidth(int width)
   *
   * 设置第columnIndex+1列的列宽，单位为字符宽度的1/256
   *    sheet.setColumnWidth(int columnIndex, int width)
   * 设置第5列的列宽为20个字符宽度
   *    sheet.setColumnWidth(4, 20*256)
   * @param sheet
   * @param colWidths 列宽字符数-变长数组
   */
  protected static void setColumnWidth(final Sheet sheet,
                                            final Integer... colWidths) {
    int col = -1;
    for(int colWidth: colWidths) {
      col++;
      sheet.setColumnWidth(col, colWidth * 256);
    }
  }

  /**
   * 创建 标题 的显示风格
   * @param sheet
   * @return
   */
  protected static CellStyle createTitleStyle(Sheet sheet) {
    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
    Font font1 = sheet.getWorkbook().createFont();
    font1.setFontHeightInPoints((short) 16);
    font1.setFontName("宋体");
    font1.setBold(true);

    cellStyle.setFont(font1);
    // 垂直对齐
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    // 水平对齐
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    return cellStyle;
  }

  protected static CellStyle createTableHeaderStyle(Sheet sheet) {
    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
    Font font1 = sheet.getWorkbook().createFont();
    font1.setFontName("宋体");
    font1.setBold(true);

    cellStyle.setFont(font1);
    // 垂直对齐
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    // 水平对齐
    cellStyle.setAlignment(HorizontalAlignment.CENTER);

    return cellStyle;
  }

  /**
   * 创建百分比格式
   * @param sheet
   * @param percentFormat "0.00%"
   * @return
   */
  protected static CellStyle createPercentCellStyle(Sheet sheet, String percentFormat) {
    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
    cellStyle.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat(percentFormat));

    return cellStyle;
  }

}
